Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
'Imports System.Collections
Imports System.Configuration
Imports Phila

Public NotInheritable Class Sql

#Region "Declarations"
    Public Shared configSetting As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
    Public Shared Connection As String = configSetting.GetValue("Connection", GetType(System.String))
    Public Shared sqlConn As SqlConnection
    Public Shared intRadio1 As Integer = 0
    Public Shared connstate As String
#End Region

#Region "Connections with database"
    Public Shared Function Connect() As SqlConnection
        Return New SqlConnection(Connection)
    End Function

    Public Shared Function OpenConnection() As SqlConnection
        sqlConn = Sql.Connect
        If sqlConn.State = ConnectionState.Closed Then sqlConn.Open()
        Return sqlConn
    End Function

    Public Shared Sub CloseConnection()
        sqlConn.Close()
    End Sub
#End Region

#Region "Insert Update Delete Function"
    Public Shared Function Insert_Update_Delete_Record(ByVal NameOfSP As String)
        Dim cmdIObject As SqlCommand = New SqlCommand
        Dim Conn As SqlConnection = Sql.Connect
        cmdIObject.Connection = Conn
        cmdIObject.CommandType = CommandType.Text 'CommandType.StoredProcedure
        cmdIObject.CommandText = NameOfSP
        'Open Connection if it is close
        Try
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            cmdIObject.ExecuteNonQuery()
            cmdIObject.Parameters.Clear()
            If Conn.State = ConnectionState.Open Then Conn.Close()
        Catch ex As Exception
            Dim str As String
            str = ex.Message
            Throw ex
        Finally
            If Conn Is Nothing Then
            ElseIf Conn.State = ConnectionState.Open Then
                Conn.Close()
            End If
            Conn.Dispose()
        End Try
        Return NameOfSP
    End Function
#End Region

#Region "Other Functions"

    'Public Shared Sub Fill_Chart(ByVal radchart As Telerik.WebControls.RadChart, ByVal strsql As String)
    '    Dim dt As DataTable
    '    dt = Fill_Table(strsql, "Chart")
    '    radchart.DataSource = dt
    '    radchart.DataBind()
    'End Sub

    Public Shared Function GetIntegerFROMString(ByVal SelectString As String) As Integer
        Dim Conn As SqlConnection = Sql.Connect
        Dim cmdIntFROMString As SqlCommand = New SqlCommand
        cmdIntFROMString.Connection = Conn
        cmdIntFROMString.CommandType = CommandType.Text
        cmdIntFROMString.CommandText = SelectString
        Try
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            If IsDBNull(cmdIntFROMString.ExecuteScalar()) = False Then
                Return cmdIntFROMString.ExecuteScalar()
            Else
                Return 0
            End If
            If Conn.State = ConnectionState.Open Then Conn.Close()
        Catch ex As Exception
            Dim strS As String
            strS = ex.Message
            Throw ex
        Finally
            If Conn Is Nothing Then
            ElseIf Conn.State = ConnectionState.Open Then
                Conn.Close()
            End If
            Conn.Dispose()
            cmdIntFROMString.Dispose()
        End Try

    End Function

    Public Shared Function Fill_Table(ByVal strSql As String, ByVal tblName As String, Optional ByVal ConnBool As Boolean = False, Optional ByVal Conn1 As SqlConnection = Nothing, Optional ByVal CmdIobject1 As SqlCommand = Nothing) As DataTable
        Dim Conn As New SqlConnection
        Dim cmdIObject As SqlCommand
        Dim SqlDA As New SqlDataAdapter
        If ConnBool = False Then
            Conn = Sql.Connect
            cmdIObject = New SqlCommand
        Else
            Conn = Conn1
            cmdIObject = CmdIobject1
        End If

        Try
            If Conn.State = ConnectionState.Closed Then Conn.Open()
            Dim va As String = Conn.ConnectionString.ToString()
            cmdIObject.CommandText = strSql.ToString
            cmdIObject.Connection = Conn
            SqlDA.SelectCommand = cmdIObject
            connstate = Conn.State.ToString
            Dim table1 As New DataTable
            table1.TableName = tblName
            table1.Rows.Clear()
            SqlDA.Fill(table1)
            Conn.Close()
            cmdIObject.Parameters.Clear()
            Return table1
        Catch ee As Exception
            If Conn.State = ConnectionState.Open Then Conn.Close()
            Conn = Nothing
            Throw New Exception("Error4: " & ee.ToString)
        Finally
            Conn.Dispose()
        End Try
    End Function

    Public Shared Function GetDataTable(ByVal query As String, Optional ByVal ConnBool As Boolean = False, Optional ByVal Conn1 As SqlConnection = Nothing, Optional ByVal CmdIobject1 As SqlCommand = Nothing) As DataTable
        Dim Conn As SqlConnection
        Dim cmdIObject As SqlCommand
        Dim SqlDA As New SqlDataAdapter
        If ConnBool = False Then
            Conn = Sql.Connect
            cmdIObject = New SqlCommand
        Else
            Conn = Conn1
            cmdIObject = CmdIobject1
        End If
        Dim table1 As New DataTable
        Try
            Dim adapter1 As New SqlDataAdapter
            cmdIObject.CommandText = query
            cmdIObject.Connection = Conn
            adapter1.SelectCommand = cmdIObject
            If ConnBool = False Then
                If Conn.State = ConnectionState.Open Or Conn.State = ConnectionState.Fetching Or Conn.State = ConnectionState.Executing Or Conn.State = ConnectionState.Connecting Then
                    Conn.Close()
                End If
                If Conn.State = ConnectionState.Closed Then Conn.Open()
            End If
            adapter1.Fill(table1)
        Finally
            If ConnBool = False Then
                If Conn Is Nothing = False Then
                    If Conn.State = ConnectionState.Open Or Conn.State = ConnectionState.Fetching Or Conn.State = ConnectionState.Executing Or Conn.State = ConnectionState.Connecting Then
                        Conn.Close()
                    End If
                End If
            End If
            Conn.Dispose()
        End Try
        Return table1
    End Function

    Public Shared Function getdataset(ByVal strsql As String) As DataSet
        Dim dataadapter As SqlDataAdapter
        Dim ds As New DataSet
        Dim sqlcmd As New SqlCommand
        Dim conn As New SqlConnection
        Try
            conn = Connect()
            conn.Open()
            sqlcmd.Connection = conn
            sqlcmd.CommandText = strsql
            dataadapter = New SqlDataAdapter(sqlcmd)
            dataadapter.Fill(ds)
            conn.Close()
            Return ds
        Catch ex As Exception
            Throw ex
        Finally
            If Connect.State = ConnectionState.Open Then
                conn.Close()
            End If
            conn.Dispose()
        End Try
    End Function
#End Region

End Class


